"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 导入导出、备份恢复工具支持M*兼容性
Case Name   : gs_dump导出导入前后测试创建视图添加definer语法
Create At   : 2023.05.04
Owner       : z@wan005
Description :
    1.创建兼容b库
    2.兼容b库创建测试用户并授权
    3.兼容b库依次创建普通表，列存表，ustore表，segment表，unlogged表并创建视图
    4.兼容b库创建分区表并创建视图
    5.兼容b库创建二级分区表并创建视图
    6.使用gs_dump工具导出表为目录归档格式
    7.导入之前导出的数据到新库
    8.连接导入库查询视图
    9.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
    8.成功，导入后视图存在，definer查询视图定义不会显示
    9.成功
History     :
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class GsDump0018(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.dbuser_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.com = Common()
        self.db_name = "db_dump_0018"
        self.db_name_01 = "db_dump_0018_01"
        self.tb_name = "tb_dump_0018"
        self.view_name = "view_dump_0018"
        self.user_name = "user_dump0018"
        self.dump_path = os.path.join(macro.DB_BACKUP_PATH, 'dump0018')

    def test_server_tools(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '----step1: 创建兼容b库 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} encoding 'UTF8' " \
                  f"dbcompatibility 'b';" \
                  f"drop database if exists {self.db_name_01};" \
                  f"create database {self.db_name_01} encoding 'UTF8' " \
                  f"dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertEqual(2, sql_result.count(
            self.constant.CREATE_DATABASE_SUCCESS), '执行失败:' + text)

        text = '---step2: 兼容b库创建测试用户并授权 expect: 成功---'
        self.log.info(text)
        sql_cmd = f'''drop user if exists {self.user_name} cascade;
        create user {self.user_name} password '{macro.PASSWD_INITIAL}';\
        grant proxy on current_user() to {self.user_name};'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.GRANT_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step3: 兼容b库依次创建普通表，列存表，ustore表，segment表，' \
               'unlogged表并创建视图 expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name},{self.tb_name}_01,\
        {self.tb_name}_02,{self.tb_name}_03,{self.tb_name}_04;
        create table {self.tb_name}(c int);
        create or replace definer = {self.user_name} view {self.view_name}(c) \
        as select c from {self.tb_name} order by c;
        create table {self.tb_name}_01(c int) with (orientation = column);
        create or replace definer = {self.user_name} view \
        {self.view_name}_01(c) as select c from {self.tb_name}_01 order by c;
        create table {self.tb_name}_02(col1 int, col2 int) \
        with (storage_type = ustore);
        create or replace definer = {self.user_name} view \
        {self.view_name}_02(col1, col2) as select col1, col2 from \
        {self.tb_name}_02 order by (col1, col2);
        create table {self.tb_name}_03(col1 int, col2 int) \
        with (segment = on);
        create or replace definer = {self.user_name} view \
        {self.view_name}_03(col1, col2) as select col1, col2 from \
        {self.tb_name}_03 order by (col1, col2);
        create unlogged table {self.tb_name}_04(col1 int, col2 int);
        create or replace definer = {self.user_name} view \
        {self.view_name}_04(col1, col2) as select col1, col2 from \
        {self.tb_name}_04 order by (col1, col2);'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(5,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(5, sql_result.count(
            self.constant.CREATE_VIEW_SUCCESS_MSG), '执行失败:' + text)

        text = '--step4: 兼容b库创建分区表并创建视图 expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''create table {self.tb_name}_05(col1 int, col2 int)\
        partition by range(col1)\
        (\
        partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000));
        create or replace definer = {self.user_name} view \
        {self.view_name}_05(col1, col2) as select * from {self.tb_name}_05\
         partition(p1, p2, p3) order by (col1, col2);
         create table {self.tb_name}_06(col1 date, col2 int)\
         partition by range(col1)interval('1 day')\
         (\
         partition p1 values less than ('2019-02-01 00:00:00'),\
         partition p2 values less than ('2019-02-02 00:00:00'),\
         partition p3 values less than ('2019-02-03 00:00:00'));
         create or replace definer = {self.user_name} view \
         {self.view_name}_06(col1, col2) as select * from {self.tb_name}_06 \
         partition(p1, p2, p3) order by (col1, col2);
         create table {self.tb_name}_07(col1 int, col2 int)\
         partition by list(col1)
         (\
         partition p1 values in (1000),\
         partition p2 values in (2000),\
         partition p3 values in (4000));
         create or replace definer = {self.user_name} view \
         {self.view_name}_07(col1, col2) as select * from {self.tb_name}_07 \
         partition(p1, p2, p3) order by (col1, col2);
         create table {self.tb_name}_08(col1 int, col2 int)\
         partition by hash(col1)\
         (\
         partition p1,\
         partition p2,\
         partition p3);
         create or replace definer = {self.user_name} view \
         {self.view_name}_08(col1, col2) as select * from {self.tb_name}_08 \
         partition(p1, p2, p3) order by (col1, col2);'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(4,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(4, sql_result.count(
            self.constant.CREATE_VIEW_SUCCESS_MSG), '执行失败:' + text)

        text = '--step5: 兼容b库创建二级分区表并创建视图 expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''create table {self.tb_name}_09(col1 int, col2 int)\
        partition by range(col1) subpartition by range(col2)\
        (\
        partition p1 values less than (1000)\
        (\
        subpartition p11 values less than (500),\
        subpartition p12 values less than (1000)\
        ),\
        partition p2 values less than (2000)\
        (\
        subpartition p21 values less than (1500),\
        subpartition p22 values less than (2000)\
        ),\
        partition p3 values less than (4000)\
        (\
        subpartition p31 values less than (3000),\
        subpartition p32 values less than (4000)\
        ));
        create or replace definer = {self.user_name} view \
        {self.view_name}_09(col1, col2) as select * from {self.tb_name}_09 \
        partition(p1, p2, p3) order by (col1, col2);'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.CREATE_VIEW_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '----step6: 使用gs_dump工具导出表为目录归档格式 expect: 成功----'
        self.log.info(text)
        gs_dump_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gs_dump " \
                      f"-p {self.dbuser_node.db_port} " \
                      f"{self.db_name} -F d " \
                      f"-f {self.dump_path}"
        self.log.info(gs_dump_cmd)
        dump_msg = self.dbuser_node.sh(gs_dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(f"dump database {self.db_name} successfully",
                      dump_msg, '执行失败:' + text)

        text = '----step7: 导入之前导出的数据到新库 expect: 成功----'
        self.log.info(text)
        restore_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gs_restore -p {self.dbuser_node.db_port} " \
                      f"-d {self.db_name_01} {self.dump_path}"
        self.log.info(restore_cmd)
        restore_msg = self.dbuser_node.sh(restore_cmd).result()
        self.log.info(restore_msg)
        self.assertIn(self.constant.RESTORE_SUCCESS_MSG, restore_msg,
                      '执行失败:' + text)

        text = '-step8: 连接导入库查询视图 expect: 成功--'
        self.log.info(text)
        sql_cmd = f"show create view {self.view_name};" \
                  f"select viewname from pg_views where viewname like  " \
                  f"'%view_%' and schemaname='public';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name_01)
        self.log.info(sql_result)
        self.assertNotIn('definer =', sql_result, '执行失败:' + text)
        self.assertIn('(10 rows)', sql_result, '执行失败:' + text)

    def tearDown(self):
        text = '---step9: 清理环境 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name_01};" \
                  f"drop user if exists {self.user_name} cascade;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname='postgres')
        self.log.info(sql_result)
        rm_cmd = f"rm -rf {self.dump_path}"
        self.log.info(rm_cmd)
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)
        self.assertEqual(2,
                         sql_result.count(self.constant.DROP_DATABASE_SUCCESS))
        self.assertEqual('', rm_msg)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
